package com.bookm.service.excel3;

import cn.hutool.json.JSONUtil;
import com.alibaba.fastjson.JSONObject;
import com.bookm.bean.Book;
import com.bookm.bean.ImportColumnInfo;
import com.bookm.service.excel2.SheetHandler;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.google.gson.Gson;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import java.util.stream.Collectors;


/**
 * 数据量比较大(8万条以上)的excel文件解析，将excel文件解析为 行列坐标-值的形式存入map中，此方式速度快，内存耗损小 但只能读取excel文件
 * 提供处理单个sheet方法 processOneSheet(String  filename) 以及处理多个sheet方法 processAllSheets(String  filename)
 * 只需传入文件路径+文件名即可  调用处理方法结束后，只需接收ExcelUtil.getRowContents()返回值即可获得解析后的数据
 */
public class ExcelUtil {
    private static LinkedHashMap<String, JSONObject> rowContents = new LinkedHashMap<String, JSONObject>();
    public static SheetHandler sheetHandler;

    public LinkedHashMap<String, JSONObject> getRowContents() {
        return rowContents;
    }

    public static void setRowContents(LinkedHashMap<String, JSONObject> rc) {
        rowContents = rc;
    }

    public SheetHandler getSheetHandler() {
        return sheetHandler;
    }

    public static void setSheetHandler(SheetHandler sh) {
        sheetHandler = sh;
    }

    public static List<LinkedHashMap<String, JSONObject>> processSheetByRId(InputStream in, Integer count) throws Exception {
        OPCPackage pkg = null;
        InputStream sheet = null;
        List<LinkedHashMap<String, JSONObject>> results = new ArrayList<>();
        try {
            pkg = OPCPackage.open(in);
            XSSFReader r = new XSSFReader(pkg);
            SharedStringsTable sst = r.getSharedStringsTable();
            StylesTable stylesTable = r.getStylesTable();
            for (int i = 0; i < count; i++) {
                sheet = r.getSheet("rId" + (i + 1));
                InputSource sheetSource = new InputSource(sheet);
                HashMap<String, ImportColumnInfo> stringImportColumnInfoHashMap = setImportColumn(new HashMap<>());
                XMLReader parser = fetchSheetParser(sst, stylesTable, stringImportColumnInfoHashMap);
                parser.parse(sheetSource);
                results.add(sheetHandler.getRowContents());
            }

            return results;
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            if (pkg != null) {
                pkg.close();
            }
            if (sheet != null) {
                sheet.close();
            }
        }
    }


    // 处理一个sheet
    public static void processOneSheet(String filename) throws Exception {
        InputStream sheet2 = null;
        OPCPackage pkg = null;
        try {
            pkg = OPCPackage.open(filename);
            XSSFReader r = new XSSFReader(pkg);
            SharedStringsTable sst = r.getSharedStringsTable();
            StylesTable stylesTable = r.getStylesTable();
            HashMap<String, ImportColumnInfo> stringImportColumnInfoHashMap = setImportColumn(new HashMap<>());
            XMLReader parser = fetchSheetParser(sst, stylesTable, stringImportColumnInfoHashMap);
            sheet2 = r.getSheet("rId1");
            InputSource sheetSource = new InputSource(sheet2);
            parser.parse(sheetSource);
            setRowContents(sheetHandler.getRowContents());
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            if (pkg != null) {
                pkg.close();
            }
            if (sheet2 != null) {
                sheet2.close();
            }
        }
    }

    // 处理多个sheet
    public static void processAllSheets(String filename) throws Exception {
        OPCPackage pkg = null;
        InputStream sheet = null;
        try {
            pkg = OPCPackage.open(filename);
            XSSFReader r = new XSSFReader(pkg);
            SharedStringsTable sst = r.getSharedStringsTable();
            StylesTable stylesTable = r.getStylesTable();
            setImportColumn(new HashMap<>());
            HashMap<String, ImportColumnInfo> stringImportColumnInfoHashMap = setImportColumn(new HashMap<>());
            XMLReader parser = fetchSheetParser(sst, stylesTable, stringImportColumnInfoHashMap);
            Iterator<InputStream> sheets = r.getSheetsData();
            while (sheets.hasNext()) {
                System.out.println("Processing new sheet:\n");
                sheet = sheets.next();
                InputSource sheetSource = new InputSource(sheet);
                parser.parse(sheetSource);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            if (pkg != null) {
                pkg.close();
            }
            if (sheet != null) {
                sheet.close();
            }
        }
    }


    public static XMLReader fetchSheetParser(SharedStringsTable sst, StylesTable sharedStringsTable, HashMap importNeedColumnMap) throws SAXException {
        XMLReader parser = XMLReaderFactory.createXMLReader("com.sun.org.apache.xerces.internal.parsers.SAXParser");
        final SheetHandler sheetHandler = new SheetHandler(sst, sharedStringsTable);
        sheetHandler.importNeedColumnMap = importNeedColumnMap;
        setSheetHandler(sheetHandler);
        ContentHandler handler = (ContentHandler) sheetHandler;
        parser.setContentHandler(handler);
        return parser;
    }

    static HashMap<String, ImportColumnInfo> setImportColumn(HashMap<String, ImportColumnInfo> icHash) {
        icHash = new HashMap<>();
        ImportColumnInfo impinfo = null;
//		List<GenColumn> genColumnList = getGenColumnByTabName(tableName);
        impinfo = new ImportColumnInfo();
        impinfo.ici_column = "type";
        impinfo.ici_xls_title = "type";
        impinfo.ici_xls_type = "String";
        icHash.put(impinfo.ici_column, impinfo);

        impinfo = new ImportColumnInfo();
        impinfo.ici_column = "name";
        impinfo.ici_xls_title = "name";
        impinfo.ici_xls_type = "String";
        icHash.put(impinfo.ici_column, impinfo);
        return icHash;
    }

    /**
     * See org.xml.sax.helpers.DefaultHandler
     */
    public static void main(String[] args) throws Exception {
        test();
//        test2();
    }

    public static void test() throws Exception {
        ExcelUtil example = new ExcelUtil();
        example.processOneSheet("data.xlsx");

        LinkedHashMap<String, JSONObject> map = example.getRowContents();

        ObjectMapper mapper = new ObjectMapper();
        List<Book> books = map.values().stream().parallel()
                .map(jsonObject -> {
                    try {
                        return mapper.readValue(jsonObject.toString(), Book.class);
                    } catch (IOException e) {
                        throw new RuntimeException(e);
                    }
                })
                .collect(Collectors.toList());
        books.forEach(item -> System.out.println(item));

    }

    public static void test2() throws Exception {
        ExcelUtil example = new ExcelUtil();
        final List<LinkedHashMap<String, JSONObject>> linkedHashMaps = example.processSheetByRId(new FileInputStream(new File("data.xlsx")), 2);
        linkedHashMaps.forEach(item->{
            System.out.println(item);
        });
    }
}


